﻿Option Strict On

Imports System.Data.SQLite
Imports System.Data

Public Class SQLiteDataLayer
    Implements System.IDisposable

    Private Property Conn As New SQLiteConnection
    Private Disposed As Boolean

    Public Property ConnectionString As String
    Public Property CommandText As String
    Public Property CommandType As CommandType
    Public Property ParameterCollection As SQLiteParamCollection

    Public Sub New(ConnectionString As String)
        Me.CommandType = Data.CommandType.Text
        Me.ConnectionString = ConnectionString
    End Sub

    Public Shared Function ParseConnectionString(ConnectionString As String) As String
        Dim SQLiteConnectionStringBuilder As New SQLiteConnectionStringBuilder
        SQLiteConnectionStringBuilder.ConnectionString = ConnectionString
        ParseConnectionString = SQLiteConnectionStringBuilder.DataSource
        SQLiteConnectionStringBuilder = Nothing
    End Function
     
    Public Function ExecuteDataTable() As DataTable
        Dim dt As New DataTable

        Try
            Using con As New SQLiteConnection(Me.ConnectionString)
                Using cmd As New SQLiteCommand(Me.CommandText, con)

                    If Me.ParameterCollection IsNot Nothing Then
                        For Each Param As SQLiteParameter In Me.ParameterCollection
                            cmd.Parameters.Add(Param)
                        Next
                    End If

                    con.Open()
                    Using sda As New SQLiteDataAdapter(cmd)
                        sda.Fill(dt)
                    End Using
                    con.Close()
                End Using
            End Using
            Return dt
        Catch ex As Exception
            Throw New Exception("SQLiteDataLayer: ExecuteDataTable()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        End Try
    End Function
    Public Function ExecuteNonQuery() As Int32
        Try
            Using con As New SQLiteConnection(Me.ConnectionString)
                Using cmd As New SQLiteCommand(Me.CommandText, con)

                    If Me.ParameterCollection IsNot Nothing Then
                        For Each Param As SQLiteParameter In Me.ParameterCollection
                            cmd.Parameters.Add(Param)
                        Next
                    End If

                    con.Open()
                    cmd.ExecuteNonQuery()


                    cmd.CommandText = "select last_insert_rowid()"
                    ExecuteNonQuery = CInt(CLng(cmd.ExecuteScalar()))

                    con.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw New Exception("SQLiteDataLayer: ExecuteNonQuery()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        End Try
    End Function
    
    Public Function ExecuteDataReader() As SQLiteDataReader
        Try
            Conn = New SQLiteConnection(Me.ConnectionString)
            Using cmd As New SQLiteCommand(Me.CommandText, Conn)
                If Me.ParameterCollection IsNot Nothing Then
                    For Each Param As SQLiteParameter In Me.ParameterCollection
                        cmd.Parameters.Add(Param)
                    Next
                End If
                Conn.Open()
                ExecuteDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            End Using

        Catch ex As Exception
            Throw New Exception("SQLiteDataLayer: ExecuteDataReader()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        End Try
    End Function

 

    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Me.Disposed = False Then
            If disposing = True Then
                ConnectionString = Nothing
                CommandText = Nothing
                ParameterCollection = Nothing
                If Conn.State = ConnectionState.Open Then
                    Conn.Close()
                    Conn.Dispose()
                End If
            End If
        End If
        Me.Disposed = True
    End Sub
    Protected Overrides Sub Finalize()
        Dispose(False)
        MyBase.Finalize()
    End Sub
    Public Sub Dispose() Implements IDisposable.Dispose
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub

End Class


Public Class SQLiteParamCollection
    Inherits System.Collections.ObjectModel.Collection(Of SQLiteParameter)
End Class